class UserItem < ActiveRecord::Base
  belongs_to :item
  
  #
  #   get user study appointed item log
  #
  def self.find_or_creatd_study_item_log(user_id,course_id,item_id)
    UserItem.find_or_create_by_user_id_and_course_id_and_item_id(user_id, course_id, item_id)
  end

  #
  #   user study items's count progress<=50
  #
  #
  def self.user_weak_items_count(user_id,course_id)
    UserItem.count_by_sql ["SELECT  COUNT(1) AS count
                          FROM  user_items
                          WHERE  user_items.user_id = ?
                          AND  user_items.course_id = ?
                          AND user_items.progress <= 50", user_id, course_id]
  end

  #
  #   user study items's count progress>50 and progress < 100
  #   
  #
  def self.user_strong_items_count(user_id,course_id)
    UserItem.count_by_sql ["SELECT  COUNT(1) AS count
                          FROM  user_items
                          WHERE  user_items.user_id = ?
                          AND  user_items.course_id = ?
                          AND user_items.progress > 50
                          AND user_items.progress < 100", user_id, course_id]
  end

  #
  #   user study completed items's count progress = 100
  #
  def self.user_completed_item_count(user_id, course_id)
    UserItem.count_by_sql ["SELECT  COUNT(1) AS count
                          FROM  user_items
                          WHERE  user_items.user_id = ?
                          AND  user_items.course_id = ?
                          AND user_items.progress = '100'", user_id, course_id]
  end

  #
  #   user not study items count
  #
  #
  def self.user_new_items_count(completed_item_count,incompleted_items_count,course)
    course.items_count - completed_item_count - incompleted_items_count
  end

  #
  #   user study not complete items progress < 100
  #
  #   @param    list_id
  #   @param    user_id
  #   @param    size      取出的最大数
  #
  def self.get_incomplete_items(list_id,user_id,size)
    sql = " SELECT *
            FROM  user_items
            WHERE  user_items.user_id = ?
            AND user_items.course_id = ?
            AND progress <100 LIMIT ?"
    UserItem.find_by_sql [sql,user_id,list_id,size]
  end

  #
  #  get user studyed appoint item
  #
  def self.user_learned_singal_item(user_id, item_id)
    UserItem.find(:first, :conditions => ["user_id = ? AND item_id = ? ", user_id, item_id])
  end

  #
  #   update user study item info
  #
  def self.update_study_info(user_item,new_progress)
    user_item.progress += new_progress
    user_item.save!
  end

  #
  #   find all user studied appointed course's all item log
  #
  def self.user_learned_items(user_id, course_id)
    UserItem.find(:all, :conditions => ["course_id = ? AND user_id = ?",course_id,user_id])
  end

  #
  #   query  start date to end date all completed items count
  #   group by day
  #
  #   @param start_date 开始时间 例如 ‘2009-8-1’
  #   @param end_date 结束时间 例如 ‘2009-8-1’
  #   return's user_id column is completed items count
  #   return's stat
  #
  def self.get_useitem_count_by_date(user_id, start_date, end_date)
    sql = " SELECT count(*) as user_id, day(updated_at) as course_id
      FROM  user_items
      WHERE user_items.progress = 100
      AND updated_at > ? AND updated_at < ? AND user_id = ?
      group by day(updated_at)"

    UserItem.find_by_sql [sql,start_date,end_date,user_id]
  end

end
